IF OBJECT_ID('dbo.RptProposalTransaction') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptProposalTransaction
    IF OBJECT_ID('dbo.RptProposalTransaction') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptProposalTransaction >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptProposalTransaction >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptProposalTransaction
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/18/2007
--
-- Description: Source of Report, "ProposalTransactionReport"
--                  In Insight DB Reporting Services
--
-- This report documents the volume of proposals processed.  It allows 
-- us to assess and compare the number of proposals over a particular deadline, 
-- fiscal year, etc.
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                           
--     @Institution                       
--     @Department                        
--     @PrincipalInvestigator                        
--     @ProposalStatus                    
--     @Sponsor                           
--     @Mechanism                         
--     @RMCaseMgr                         
--     @BudgetPeriodStatus                     
--     @domainUserId                      
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptProposalTransaction()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/18/07     KC        Created
-- 7/25/07     KC        Changed Naming Conventions, readied for QA
-- 8/7/07      KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 9/8/07      KC        Changed to new source tables
-- 9/20/07     KC        Fixed SponsorAgreementID, Added @PreAwardManager
--                       Removed @Mechanism
-- 9/26/07     KC        Added @Unit
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 10/11/07    KC        Added JOIN for PropTransactions, Amendments
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptProposalTransaction
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL,             
     @PreAwardManager                   VARCHAR(100) = NULL,
     @PostAwardManager                  VARCHAR(100) = NULL,
     @Sponsor                           VARCHAR(100) = NULL,
     @ProposalStatus                    VARCHAR(100) = NULL,
     @BudgetStatus                      VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     IF @ProposalStatus IN ('ALL','','0') SET @ProposalStatus = NULL
     IF @BudgetStatus IN ('ALL','','0') SET @BudgetStatus = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Result Set
     SELECT    PT.FolderNumber AS ProposalNumber,        
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               CASE WHEN PT.TransactionType = 'Master' 
                    THEN AI.ProposalTitle
                    ELSE PT.TransactionType + ': ' + PT.Proj_Title
               END AS ProposalTitle,                 
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS Unit,
               AI.SponsorName AS SponsorName,        
               AI.SponsorAgreementID AS SponsorNumber,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               BUDG.BudgetViewType AS BudgetPeriodStatus,
               PROJ.ProjectDirectCost AS DirectCostforProjectPeriod,
               BUDG.BudgetDirectCost AS DirectCostforBudgetPeriod,
               PROJ.ProjectIndirectCost AS FAforProjectPeriod,
               BUDG.BudgetIndirectCost AS FAforBudgetPeriod,
               AI.ProposalStatus AS ProposalStatus,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,  
               AI.InstrumentType AS ProposalType,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               UDFDel.DueDate AS SponsorDueDate,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,'Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Pre Award Manager Not Assigned') AS PreAwardManagerName,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN 

               -- ProjectDirectCosts have to be summed from the Budget tables
               -- Over the life of the Agreement (Project)
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId LEFT OUTER JOIN
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN

               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId INNER JOIN

               -- Need Deliverables for DueDate Search                         
               dbo.vwInfoEdDeliverables UDFDel ON AI.InfoEdPropNumber = UDFDel.prop_no LEFT OUTER JOIN

               -- Need Proposal Transactions
               (SELECT   P.inst_no AS FolderNumber,
                         CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                              THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                              ELSE P.inst_no 
                         END AS ParentFolderNumber, 
                         C.code_desc AS TransactionType, 
                         P.Proj_Title
               FROM dbo.InfoEdProposal P INNER JOIN dbo.InfoEdCodetab C ON P.prop_type = C.code AND C.arr_name = 'PROP_TYPE' 
                         AND RTRIM(C.code) != '9' /* Ignore 'Original Submission' */ ) PT 
                         ON AI.FolderNumber = PT.ParentFolderNumber 
                         
     WHERE     (UDFDel.DueDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
               (@ProposalStatus IS NULL OR AI.AgreementStatusId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @ProposalStatus ))) AND
               (@BudgetStatus IS NULL OR BUDG.BudgetViewType IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @BudgetStatus ))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager ))) 
     ORDER BY AI.PrincipalInvestigator

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements 
     
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptProposalTransaction') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptProposalTransaction >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptProposalTransaction >>>'
go

GRANT EXECUTE ON dbo.RptProposalTransaction TO InsightReport,Insight
go
